﻿namespace DCHealthCheck.DomainModel {

    #region Usings
    using DCHealthCheck.Common;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.Validation;
    using System.Data.Objects;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Security.Principal;
    using System.Text;
    using System.Threading;
    using System.Transactions;
    #endregion

    public class EntityManager : DCHealthCheckEntities, IEntityManager {

        #region Members
        /// <summary>
        /// TransactionOptions for large numbers of changes
        /// </summary>
        private TransactionOptions BulkTransactionOptions;

        private static readonly log4net.ILog DatabaseLog = log4net.LogManager.GetLogger("databaseLogger");

        /// <summary>
        /// The last error that occurred.
        /// </summary>
        public Exception LastError { get; private set; }

        /// <summary>
        /// Returns this as IObjectContextAdapter.ObjectContext, for the purpose of accessing the traditional ObjectContext
        /// </summary>
        public new ObjectContext ObjectContext { get { return base.ObjectContext; } }

        /// <summary>
        /// Specifies if an audit is to be performed, based on criteria.
        /// </summary>
        private bool PerformAudit {
            get {
                return (this.UserIdentity != null) &&
                    (!this.UserIdentity.Name.ToUpper().Contains("APP-")) &&
                    (!this.UserIdentity.Name.ToUpper().Contains("SVC-")) &&
                    (!this.UserIdentity.Name.ToUpper().Contains("NT AUTHORITY"));
            }
        }

        /// <summary>
        /// Used to validate that this instance is not used by other processes/threads
        /// </summary>
        private readonly int ProcessId;

        /// <summary>
        /// Complex type used to encapsulate the list of IQueryable set definitions
        /// </summary>
        public QuerySets QuerySets {
            get { return querySets; }
        }
        private readonly QuerySets querySets;

        /// <summary>
        /// The maximum value for a SQL time column
        /// </summary>
        public static readonly TimeSpan SqlTimeMaxValue = TimeSpan.FromHours(24).Subtract(TimeSpan.FromTicks(1));

        /// <summary>
        /// Used to validate that this instance is not used by other processes/threads
        /// </summary>
        private readonly int ThreadId;

        /// <summary>
        /// The default TransactionOptions
        /// </summary>
        private TransactionOptions TransactionOptions;

        /// <summary>
        /// The identity associated with the operations performed by the EntityManager
        /// </summary>
        internal System.Security.Principal.IIdentity UserIdentity { get; private set; }

        public new List<DbEntityValidationResult> ValidationSummary { get { return base.ValidationSummary; } }
        #endregion

        #region Constructors
        public EntityManager()
            : this(null) {
        }
        public EntityManager(IIdentity userIdentity)
            : base() {

            this.ProcessId = System.Diagnostics.Process.GetCurrentProcess().Id;
            this.ThreadId = Thread.CurrentThread.ManagedThreadId;

            this.UserIdentity = userIdentity != null ? userIdentity : WindowsIdentity.GetCurrent();

            this.ObjectContext.CommandTimeout = 600;

            // Default isolation level for TransactionScope is Serializable
            TransactionOptions = new TransactionOptions() {
                IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
                Timeout = new TimeSpan(0, 0, this.ObjectContext.CommandTimeout.Value)
            };

            BulkTransactionOptions = new TransactionOptions() {
                IsolationLevel = System.Transactions.IsolationLevel.Serializable,
                Timeout = new TimeSpan(2, 0, 0)
            };

            if (this.Database.Connection.State == ConnectionState.Closed) {
                this.Database.Connection.Open();
            }

            // set default IsolationLevel to ReadCommitted.  Normally with the implicit TransactionScope, it is Serializable
            // http://blogs.u2u.be/diederik/post/2010/06/29/Transactions-and-Connections-in-Entity-Framework-40.aspx
            this.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

            querySets = new QuerySets(this);
        }
        #endregion

        #region Methods
        /// <summary>
        /// Sets the change tracker entry EntityState to match the DetachedState
        /// </summary>
        /// <remarks>
        /// From "Programming Entity Framework: DbContext", Chapter 4: Working with Disconnected Entities Including N-Tier Applications:
        /// adding the root of a graph will cause every entity in the graph to be registered with the context as a new entity. 
        /// This behavior is the same if you use DbSet.Add or change the Stateproperty for an entity to Added. Once all the entities 
        /// are tracked by the state manager, you can then work your way around the graph, specifying the correct state for each entity. 
        /// It is possible to start by calling an operation that will register the root as an existing entity. This includes  
        /// DbSet.Attachor setting the Stateproperty to Unchanged,  Modified, or  Deleted. However, this approach isn’t recommended 
        /// because you run the risk of exceptions due to duplicate key values if you have added entities in your graph. If you register 
        /// the root as an existing entity, every entity in the graph will get registered as an existing entity. 
        /// 
        /// Because existing entities should all have unique primary keys, Entity Framework will ensure that you don’t register two 
        /// existing entities of the same type with the same key. If you have a new entity instance that will have its primary key value 
        /// generated by the database, you probably won’t bother assigning a value to the primary key; you’ll just leave it set to the
        /// default value. This means if your graph contains multiple new entities of the same type, they will have the same key value. 
        /// If you attach the root, Entity Framework will attempt to mark every entity as Unchanged, which will fail because you would 
        /// have two existing entities with the same key.
        /// 
        /// If you are Adding or Attaching a Root, then painting state throughout graph, It is recommended that you Add the root rather 
        /// than attaching it to avoid key conflicts for new entities.
        /// 
        /// After the root is Added, the ConvertState will perform the fixup.
        /// </remarks>
        private void AdjustChangeTrackerState() {

            foreach (var changeTrackerEntry in this.ChangeTracker.Entries()) {
                IObjectWithState stateInfo = changeTrackerEntry.Entity as IObjectWithState;
                if (stateInfo == null) {
                    throw new NotSupportedException("Entity objects must implement IObjectWithState.");
                }

                var convertedState = ConvertState(stateInfo.DetachedState);
                if (convertedState != changeTrackerEntry.State) {
                    // do not convert state if our custom state is Unchanged but the EntityState is Deleted.  
                    // EntityState may be deleted for relationship fixup during cascade delete.
                    if (changeTrackerEntry.State != EntityState.Deleted) {
                        // do not convert EntityState to our custom state if custom state is Unchanged but the EntityState is Modified.  
                        // EntityState may be modified for relationship fixup.
                        if (!((stateInfo.DetachedState == DetachedState.Unchanged) && (changeTrackerEntry.State == EntityState.Modified))) {
                            changeTrackerEntry.State = convertedState;
                        }
                    }
                    else {
                        // Do not set our custom DetachedState to match EntityState.Deleted.  It isn't necessary, and 
                        // During SaveChanges(), this will result in the following exception:
                        // System.Data.Entity.Infrastructure.DbUpdateConcurrencyException - InnerException: System.Data.OptimisticConcurrencyException
                        // Store update, insert, or delete statement affected an unexpected number of rows (0). 
                        // Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.
                        //
                        // stateInfo.DetachedState = State.Deleted;
                    }
                }

                // this needs to be performed even if the CHANGETRACKEREntry.State == EntityState.Deleted (converted previously)
                if (stateInfo.DetachedState == DetachedState.Unchanged) {
                    // stateInfo.StartingOriginalValues would only exist if the entity had materialized in a database query
                    // adding an entity then updating it may result in a null StartingOriginalValues
                    if (stateInfo.StartingOriginalValues != null) {
                        this.ApplyPropertyChanges(changeTrackerEntry.OriginalValues, stateInfo.StartingOriginalValues);
                    }
                    else {
                        // do not mark entire entity as modified.  That would result in all properties sent to the database during update.
                        // changeTrackerEntry.State = EntityState.Modified;
                    }
                }
            }
        }

        /// <summary>
        /// Restores the OriginalValues collection from the StartingOriginalValues created at the time of entity inception
        /// </summary>
        /// <remarks>Required for concurrency checks.  When SaveChanges is called, it compares the original/current values to determine 
        /// if an entity is modified and which properties to send in the update.
        /// </remarks>
        private void ApplyPropertyChanges(DbPropertyValues values, Dictionary<string, object> startingOriginalValues) {
            foreach (var entry in startingOriginalValues) {
                var childStartingOriginalValues = entry.Value as Dictionary<string, object>;
                if (childStartingOriginalValues != null) {
                    this.ApplyPropertyChanges((DbPropertyValues)values[entry.Key], childStartingOriginalValues);
                }
                else {
                    values[entry.Key] = entry.Value;
                }
            }
        }

        /// <summary>
        /// Attaches an entity to the ObjectContext
        /// </summary>
        /// <typeparam name="TEntity">The entity type</typeparam>
        /// <param name="entity">The entity object</param>
        /// <returns>DbEntityEntry of type TEntity</returns>
        /// <remarks>Useful for attaching "stub" entities, typically created for the purpose of deleting an entity with only the entity key</remarks>
        public DbEntityEntry<TEntity> Attach<TEntity>(TEntity entity) where TEntity : class, IObjectWithState {
            // Check if the same entity with same key values are already there
            DbEntityEntry<TEntity> changeTrackerEntry = this.Entry<TEntity>(entity);

            if ((changeTrackerEntry == null) || (changeTrackerEntry.State == EntityState.Detached)) {
                // throws InvalidOperationException if entity had been active in the current context but not detached
                this.Set<TEntity>().Attach(entity);
                changeTrackerEntry = this.Entry<TEntity>(entity);
            }

            return changeTrackerEntry;
        }

        /// <summary>
        /// Converts the entity change tracker state to the state reported from the client.
        /// Modified entities are marked as Unchanged.
        /// </summary>
        private EntityState ConvertState(DomainModel.DetachedState state) {
            switch (state) {
                case DetachedState.Added:
                    return EntityState.Added;
                case DetachedState.Deleted:
                    return EntityState.Deleted;
                default:
                    return EntityState.Unchanged;
            }
        }

        public void Detach(IObjectWithState entity) {
            this.Detach(entity, true);
        }
        public void Detach(IObjectWithState entity, bool detachChildren) {
            DbEntityEntry changeTrackerEntry = this.Entry(entity);
            if (changeTrackerEntry != null) {
                if (detachChildren) {
                    var entityType = changeTrackerEntry.Entity.GetType();
                    foreach (var item in entityType.GetProperties()) {
                        // a timestamp byte[] may be considered an IList or ICollection
                        if (item.GetValue(changeTrackerEntry.Entity) as Byte[] != null) continue;
                        var navigationPropertyCollection = item.GetValue(changeTrackerEntry.Entity) as System.Collections.IList;
                        if (navigationPropertyCollection != null) {
                            for (int index = 0; index < navigationPropertyCollection.Count; index++) {
                                this.Detach(navigationPropertyCollection[index] as IObjectWithState, detachChildren);
                            }
                        }
                    }
                }

                if (changeTrackerEntry.State != EntityState.Detached) {
                    this.ObjectContext.Detach(changeTrackerEntry.Entity);
                }
            }
        }

        /// <summary>
        /// Gets the validation result for the specified entity
        /// </summary>
        /// <typeparam name="TEntity">The entity type</typeparam>
        /// <param name="entity">The entity object</param>
        /// <returns>The DbEntityValidationResult</returns>
        public DbEntityValidationResult GetValidationResult<TEntity>(TEntity entity) where TEntity : class, IObjectWithState {
            DbEntityValidationResult result = null;
            var entry = this.Attach<TEntity>(entity);
            if (entry != null) result = entry.GetValidationResult();
            return result;
        }

        /// <summary>
        /// Resets the DetachedState to Unchanged and rebuilds StartingOriginalValues
        /// </summary>
        private void ResetStateAndOriginalValues() {
            foreach (var changeTrackerEntry in this.ChangeTracker.Entries<IObjectWithState>()) {
                IObjectWithState stateInfo = changeTrackerEntry.Entity as IObjectWithState;

                if ((stateInfo != null) && (stateInfo.DetachedState != DetachedState.Deleted)) {
                    if (changeTrackerEntry.OriginalValues != null) {
                        stateInfo.StartingOriginalValues = base.BuildOriginalValues(changeTrackerEntry.OriginalValues);
                    }

                    stateInfo.DetachedState = DetachedState.Unchanged;
                }

                changeTrackerEntry.State = EntityState.Unchanged;
            }
        }

        /// <summary>
        /// Saves changes to objects in the graph
        /// </summary>
        /// <remarks>The entity should be added/attached/deleted to/from the contet before saving changes.
        /// If we have a large collection of new entities and we are confident that the default change detection is not required,
        /// disabling  the change tracking options: SaveOptions.DetectChangesBeforeSave | SaveOptions.AcceptAllChangesAfterSave
        /// will yield significant performance improvement.
        /// </remarks>
        public override int SaveChanges() {
            return this.SaveChanges(false);
        }
        public int SaveChanges(bool bulkUpdate) {
            int changesSaved = 0;

#if DEBUG
            this.ValidateProcessAndThreadId();
#endif

            this.AdjustChangeTrackerState();

            // DetectChanges also performs relationship fix-up for any relationships that it detects have changed. 
            // It ensures that the navigation properties and foreign key properties are synchronized.
            this.ChangeTracker.DetectChanges();

            #region Create Audit entry
            if (this.PerformAudit) {
                this.GetChangeTrackerEntriesDetail();
                if (this.ChangeTrackerDetails.Count > 0) {
                    var audit = this.Set<Audit>().Create();
                    audit.DateTimeUTC = DateTime.UtcNow;
                    audit.Username = this.UserIdentity.Name;
                    audit.AuditEntryText = this.ChangeTrackerDetails.ToFormattedString();
                    audit.CompressAuditData();
                    this.Set<Audit>().Add(audit);
                }
            }
            #endregion

            if (bulkUpdate) {
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew, this.BulkTransactionOptions)) {
                    changesSaved = this.ObjectContext.SaveChanges(SaveOptions.None);
                    transactionScope.Complete();

                    // If the transaction does not fail, accept the changes.  If it fails, it may still be possible to recover
                    // as the changes have not yet been discarded.
                    // http://blogs.msdn.com/b/alexj/archive/2009/01/11/savechanges-false.aspx
                    this.ObjectContext.AcceptAllChanges();
                }
            }
            else {
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, this.TransactionOptions)) {
                    changesSaved = base.SaveChanges();
                    transactionScope.Complete();
                }
            }

            this.ResetStateAndOriginalValues();

            return changesSaved;
        }

        /// <summary>
        /// Validate that this instance is not used by other processes/threads
        /// </summary>
        private void ValidateProcessAndThreadId() {
            if ((this.ProcessId > 0) && (this.ThreadId > 0)) {
                var currentProcessId = System.Diagnostics.Process.GetCurrentProcess().Id;
                var currentThreadId = Thread.CurrentThread.ManagedThreadId;

                if ((currentProcessId != this.ProcessId) || (currentThreadId != this.ThreadId)) {
                    throw new NotSupportedException("Attempt to use Entity context from another process/thread from the creating process/thread.");
                }
            }
        }

        #region Native database methods
        /// <summary>
        /// Provides mechanism to run native T-SQL against database
        /// </summary>
        /// <returns>number of rows affected or -2 if error</returns>
        /// <remarks>Example use is to truncate a table or delete large numbers of rows, bulk import of data.</remarks>
        public int ExecuteNonQuerySql(SqlCommand sqlCommand) {
            sqlCommand.Connection = this.Database.Connection as System.Data.SqlClient.SqlConnection;

            int returnValue = 0;
            try {
                if (sqlCommand.Connection.State != ConnectionState.Open) {
                    sqlCommand.Connection.Open();
                }

                returnValue = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception e) {
                DatabaseLog.ErrorFormat("[ThreadId: {0}] {1} SQL command: {2} TimeoutSeconds: {3} Error: {4}",
                    Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), sqlCommand.CommandText, sqlCommand.CommandTimeout, e.VerboseExceptionString());
                this.LastError = e;
                return -2;
            }
            return returnValue;
        }
        /// <summary>
        /// Provides mechanism to run native T-SQL against database
        /// </summary>
        /// <param name="sql"></param>
        /// <remarks>Example use is to truncate a table or delete large numbers of rows, bulk import of data.</remarks>
        /// <returns>A single object for the scalar value to return</returns>
        public object ExecuteScalarSql(SqlCommand sqlCommand) {
            sqlCommand.Connection = this.Database.Connection as System.Data.SqlClient.SqlConnection;
            object returnValue = null;

            try {
                if (sqlCommand.Connection.State != ConnectionState.Open) {
                    sqlCommand.Connection.Open();
                }

                returnValue = sqlCommand.ExecuteScalar();
            }
            catch (Exception e) {
                DatabaseLog.ErrorFormat("[ThreadId: {0}] {1} Error executing scalar SQL command: {2}", Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), e.VerboseExceptionString());
                this.LastError = e;
            }

            if ((returnValue == null) || (returnValue.GetType() == typeof(System.DBNull))) return null;
            return returnValue;
        }

        /// <summary>
        /// Provides mechanism to run native T-SQL against database and return a table
        /// For use when not returning a typed entity set.
        /// </summary>
        public DataTable ExecuteSql(SqlCommand sqlCommand) {
            sqlCommand.Connection = this.Database.Connection as System.Data.SqlClient.SqlConnection;
            DataTable dataTable = new DataTable();

            try {
                if (sqlCommand.Connection.State != ConnectionState.Open) {
                    sqlCommand.Connection.Open();
                }

                using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader()) {
                    dataTable.Load(sqlDataReader);
                }
            }
            catch (Exception e) {
                DatabaseLog.ErrorFormat("[ThreadId: {0}] {1} Error executing SQL command: {2}", Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), e.VerboseExceptionString());
                this.LastError = e;
            }

            return dataTable;
        }
        /// <summary>
        /// Executes a query directly against the data source and returns a sequence
        /// of typed results. Specify the entity set and the merge option so that query
        /// results can be tracked as entities.
        /// </summary>
        public ObjectResult<TEntity> ExecuteSql<TEntity>(string sql, params object[] parameters) where TEntity : class {
            return this.ObjectContext.ExecuteStoreQuery<TEntity>(sql, parameters);
        }
        public ObjectResult<TEntity> ExecuteSql<TEntity>(string sql, string entitySetName, MergeOption mergeOption, params object[] parameters) where TEntity : class {
            return this.ObjectContext.ExecuteStoreQuery<TEntity>(sql, entitySetName, mergeOption, parameters);
        }
        /// <summary>
        /// Executes the given stored procedure or function that is defined in the data
        /// source and expressed in the conceptual model, with the specified parameters
        /// </summary>
        public ObjectResult<TEntity> ExecuteStoredProcedure<TEntity>(string functionName, params ObjectParameter[] parameters) where TEntity : class {
            return this.ExecuteStoredProcedure<TEntity>(functionName, parameters);
        }
        public ObjectResult<TEntity> ExecuteStoredProcedure<TEntity>(string functionName, MergeOption mergeOption, params ObjectParameter[] parameters) where TEntity : class {
            return this.ObjectContext.ExecuteFunction<TEntity>(functionName, mergeOption, parameters);
        }

        /// <summary>
        /// Gets the last identity used for a table that uses identity keys
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public long GetLastIdentityKey(string tableName) {
            DatabaseLog.DebugFormat("[ThreadId: {0}] {1} ", Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName());
            if (string.IsNullOrWhiteSpace(tableName)) return -1;

            DateTime startTime = DateTime.UtcNow;
            long lastIdentityKey = -1;
            int timeoutSeconds = 120;

            try {

                using (SqlCommand sqlCommand = new SqlCommand()) {
                    sqlCommand.CommandText = string.Format("SELECT IDENT_CURRENT('{0}')", tableName);
                    sqlCommand.CommandTimeout = timeoutSeconds;

                    DatabaseLog.DebugFormat("[ThreadId: {0}] {1} Executing command: {2}",
                       Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), sqlCommand.CommandText);

                    object returnValue = this.ExecuteScalarSql(sqlCommand);
                    if (returnValue != null) {
                        try {
                            lastIdentityKey = Convert.ToInt64(returnValue);
                        }
                        catch { }
                    }
                }

                DatabaseLog.DebugFormat("[ThreadId: {0}] {1} finished.  Return value: {2}",
                   Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), lastIdentityKey);
            }
            catch (Exception e) {
                DatabaseLog.ErrorFormat("[ThreadId: {0}] {1} {2}", Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), e.VerboseExceptionString());
                this.LastError = e;
                return -1;
            }
            finally {
                DatabaseLog.DebugFormat("[ThreadId: {0}] {1} finished.  Time required: {2}",
                   Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), DateTime.UtcNow.Subtract(startTime).ToString());
            }

            return lastIdentityKey;
        }
        /// <summary>
        /// Get the fragmentation percentage of an index.  
        /// </summary>
        /// <remarks>Typically used to determine if fragmentation is above a threshold to determine if a reorganize or rebuild is appropriate.
        /// Requires permissions:
        /// VIEW DATABASE STATE
        /// VIEW SERVER STATE
        /// </remarks>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public int GetIndexFragmentation(string databaseName, string tableName, string indexName) {
            DatabaseLog.InfoFormat("[ThreadId: {0}] {1} ", Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName());

            if (string.IsNullOrWhiteSpace(databaseName) || string.IsNullOrWhiteSpace(tableName) || string.IsNullOrWhiteSpace(indexName)) {
                throw new ApplicationException("One or more parameters are not specified.");
            }

            int fragmentationPercentage = -1;
            DateTime startTime = DateTime.UtcNow;
            int commandTimeoutSeconds = 600;

            StringBuilder sqlCommandText = new StringBuilder();

            sqlCommandText.Append(string.Format("USE {0}; ", databaseName));
            sqlCommandText.Append("DECLARE @db_id SMALLINT; ");
            sqlCommandText.Append("DECLARE @object_id INT; ");
            sqlCommandText.Append(string.Format("SET @db_id = DB_ID(N'{0}'); ", databaseName));
            sqlCommandText.Append(string.Format("SET @object_id = OBJECT_ID(N'{0}'); ", tableName));

            sqlCommandText.Append("  SELECT object_name(IPS.object_id) AS TableName, ");
            sqlCommandText.Append("    SI.name AS IndexName, ");
            sqlCommandText.Append("    IPS.Index_type_desc, ");
            sqlCommandText.Append("    IPS.avg_fragmentation_in_percent, ");
            sqlCommandText.Append("    IPS.avg_fragment_size_in_pages, ");
            sqlCommandText.Append("    IPS.avg_page_space_used_in_percent, ");
            sqlCommandText.Append("    IPS.record_count, ");
            sqlCommandText.Append("    IPS.ghost_record_count, ");
            sqlCommandText.Append("    IPS.fragment_count, ");
            sqlCommandText.Append("    IPS.avg_fragment_size_in_pages ");
            sqlCommandText.Append("  FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED') AS IPS ");
            sqlCommandText.Append("  INNER JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id  ");

            try {
                DatabaseLog.DebugFormat("[ThreadId: {0}] {1} Executing command: {2} {3}",
                   Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), Environment.NewLine, sqlCommandText.ToString());

                using (SqlCommand sqlCommand = new SqlCommand(sqlCommandText.ToString())) {
                    sqlCommand.CommandTimeout = commandTimeoutSeconds;
                    DataTable dataTable = this.ExecuteSql(sqlCommand);

                    if (dataTable != null) {
                        for (int index = 0; index < dataTable.Rows.Count; index++) {
                            string currentTableName = dataTable.Rows[index]["TableName"].ToString();
                            if (currentTableName == tableName) {
                                string currentIndexName = dataTable.Rows[index]["IndexName"].ToString();
                                if (currentIndexName == indexName) {
                                    fragmentationPercentage = Convert.ToInt32(dataTable.Rows[index]["avg_fragmentation_in_percent"]);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception e) {
                DatabaseLog.ErrorFormat("[ThreadId: {0}] {1} {2}", Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), e.VerboseExceptionString());
                this.LastError = e;
            }
            finally {
                DatabaseLog.DebugFormat("[ThreadId: {0}] {1} finished.  Time required: {2}",
                   Thread.CurrentThread.ManagedThreadId, Extensions.CurrentMethodName(), DateTime.UtcNow.Subtract(startTime).ToString());
            }

            return fragmentationPercentage;
        }
        #endregion

        #region IQueryable compiled query launchers
        private IQueryable<TReturnType> RunQuery<TReturnType>(IQueryable<TReturnType> query) {
            return this.RunQuery(query, MergeOption.NoTracking);
        }
        private IQueryable<TReturnType> RunQuery<TReturnType>(IQueryable<TReturnType> query, MergeOption mergeOption) {
            var objectQuery = query as ObjectQuery<TReturnType>;
            objectQuery.EnablePlanCaching = true;
            return objectQuery.Execute(mergeOption).AsQueryable();
        }
        public IQueryable<TReturnType> Query<TReturnType>(Func<DCHealthCheckEntities, IQueryable<TReturnType>> query) {
            return RunQuery(query(this));
        }
        public IQueryable<TReturnType> Query<TReturnType>(Func<DCHealthCheckEntities, IQueryable<TReturnType>> query, MergeOption mergeOption) {
            return RunQuery(query(this), mergeOption);
        }

        public IQueryable<TReturnType> Query<Arg0, TReturnType>(Func<DCHealthCheckEntities, Arg0, IQueryable<TReturnType>> query, Arg0 arg0) {
            return RunQuery(query(this, arg0));
        }
        public IQueryable<TReturnType> Query<Arg0, TReturnType>(Func<DCHealthCheckEntities, Arg0, IQueryable<TReturnType>> query, Arg0 arg0, MergeOption mergeOption) {
            return RunQuery(query(this, arg0), mergeOption);
        }

        public IQueryable<TReturnType> Query<Arg0, Arg1, TReturnType>(Func<DCHealthCheckEntities, Arg0, Arg1, IQueryable<TReturnType>> query, Arg0 arg0, Arg1 arg1) {
            return RunQuery(query(this, arg0, arg1));
        }
        public IQueryable<TReturnType> Query<Arg0, Arg1, TReturnType>(Func<DCHealthCheckEntities, Arg0, Arg1, IQueryable<TReturnType>> query, Arg0 arg0, Arg1 arg1, MergeOption mergeOption) {
            return RunQuery(query(this, arg0, arg1), mergeOption);
        }

        public IQueryable<TReturnType> Query<Arg0, Arg1, Arg2, TReturnType>(Func<DCHealthCheckEntities, Arg0, Arg1, Arg2, IQueryable<TReturnType>> query, Arg0 arg0, Arg1 arg1, Arg2 arg2) {
            return RunQuery(query(this, arg0, arg1, arg2));
        }
        public IQueryable<TReturnType> Query<Arg0, Arg1, Arg2, TReturnType>(Func<DCHealthCheckEntities, Arg0, Arg1, Arg2, IQueryable<TReturnType>> query, Arg0 arg0, Arg1 arg1, Arg2 arg2, MergeOption mergeOption) {
            return RunQuery(query(this, arg0, arg1, arg2), mergeOption);
        }
        #endregion

        #endregion
    }
}
